/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package db;

import models.Person;
import models.Contingent;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;


/**
 *
 * @author pelle
 * @Edited by Jimmi
 * 
 * 21-06-2011, Pelle: Added getMissingContingentCount()
 */
public class ContingentDb extends AbstractDb implements ContingentInterface {

    private Object Contingent;
    private Iterable<Person> person;
    private int ContingentId;
    private String personId;




    public int createContingent(Contingent contingent) {
        int contingentId = -1;

                int res = this.db.queryInsert(""
                        + "INSERT INTO Lif_Contingent "
                        + "(contingent_paid, contingent_date, contingent_id) "
                        + "VALUES ("
                        // 14 days paytime
                        + "DATEADD(day, 14, GETDATE()), "
                        + contingent.getAmount() + ", "
                        + "0, "
                        + ");");

                if(res > 0) {
                    int conId = this.db.getInsertId();

                        String query = ""
                            + "INSERT INTO Lif_Contingent "
                            + "(contingent_id) "
                            + "(INSERT INTO Lif_Person"
                            + "(person_id)"
                            + "VALUES ";

                        String queryValues = "";
                            for(Person o : person) {
                                if(queryValues.length() > 0) {
                                    queryValues += ", ";
                                }

                                queryValues += " (" + conId + ", " + o.getId() + ")";
                            }

                    int res2 = this.db.queryInsert(query + queryValues);

                    if(res2 > 0) {
                        contingentId = conId;
                    }
                }



        return contingentId;
    }

public int deleteContingent(int id)
	{
               int rc=-1;

	  	String query="DELETE FROM contingent "
                        + "WHERE id = '" + id + "'";
                System.out.println(query);
	  	try{ // delete from contingent


	 	  	rc = this.db.queryUpdate(query);

  		}//slut try
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in contingent db: "+ex);
   	        }
		return(rc);
	}
 
    
 public int updateContingent(Contingent con)
	{
		//Contingent conObj  = con;
		int rc=-1;

		String query="UPDATE Contingent SET "+
                          "contingent_date ='"+ con.getdate()+"', "+
                          "contingent_paid ='"+ con.getPaymentStatus()+"', "+
                          "WHERE contingent_id = " + con.getId() + ";";
                try{ // update customer
	 		rc = this.db.queryUpdate(query);
		}//slut try
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in contingent db: "+ex);
	  	}
		return(rc);
	}    


public Contingent getContingentById(int id) {
        Contingent contingent = null;
        if(id > 0) {
            ResultSet rs = this.db.query(""
                    + "SELECT * "
                    + "FROM Lif_Contingent "
                    + "WHERE contingent_id = " + id);
            
              }

        return contingent;
        
        }
 
 

 
 
 
 public ArrayList<Contingent> getContingentsByPerson(Person person) {
     ArrayList<Contingent> con = new ArrayList<Contingent>();
        
        if(person != null) {
            try {
                ResultSet rs = this.db.query(""
                        + "SELECT * "
                        + "FROM Lif_Contingent "
                        + "JOIN Lif_Person ON contingent_personid = person_id "
                        + "WHERE person_id = " + person.getId());
                
                if(rs != null && rs.next()) {
                    // Build rolesarray
                    con = this.buildContingents(rs);
                }
            } catch (SQLException ex) {
                Logger.getLogger(LoginDb.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        
        return con;
   
 }

    private ArrayList<Contingent> buildContingents(ResultSet rs) {
    ArrayList<Contingent> cons = new ArrayList<Contingent>();  
        Contingent contingent = null;
        if(rs != null) {
            try {
                int userId = rs.getInt("contingent_id");
                if(userId > 0) {

                    contingent = new Contingent(
                        rs.getInt("person_id"),
                        rs.getString("contingent_date"),
                        rs.getInt("contingent_paid"));
                    
         
                    
                }
            } catch (SQLException ex) {
                Logger.getLogger(LoginDb.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return cons;
    }


    public int getMissingContingentCount() {
        int count = 0;
        ResultSet rs = this.db.query("SELECT COUNT(*) AS count FROM Lif_Contingent WHERE contingent_paid = 0");
        
        try {
            if(rs != null && rs.next()) {
                count = rs.getInt("count");
            }
        } catch (SQLException ex) {
            Logger.getLogger(PersonDb.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return count;
    }

    
    
}
